package com.budwk.app.sqfw.services.datamange.impl;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.budwk.app.exception.ServiceException;
import com.budwk.app.sqfw.dto.TreeDTO;
import com.budwk.app.sqfw.dto.onlineExcel.SqfwExcelOnlineAllDTO;
import com.budwk.app.sqfw.models.PersonInfo;
import com.budwk.app.sqfw.models.Sqfw_datamange_person;
import com.budwk.app.sqfw.services.PersonInfoService;
import com.budwk.app.sqfw.services.datamange.DataMangePersonService;
import com.budwk.app.sqfw.vo.SqfwDataMangePersonVO;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.adminUtils.CommonCheckUtils;
import com.budwk.starter.common.page.Pagination;
import com.budwk.starter.common.result.Result;
import com.budwk.starter.database.service.BaseServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.annotation.Column;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.random.R;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;

import static com.budwk.app.util.poiUtils.CommonFormOfficeUtils.getTemplatePathByOS;

/**
 * @BelongsProject: dfxy-shequ
 * @Author: yangweimin
 * @CreateTime: 2024-07-04  10:14
 * @Description: TODO
 */
@Slf4j
@IocBean(args = {"refer:dao"})
public class DataMangePersonServiceImpl extends BaseServiceImpl<Sqfw_datamange_person> implements DataMangePersonService {
    public DataMangePersonServiceImpl(Dao dao) {
        super(dao);
    }
    private static final Integer pageSize = 200;

    private static Map<String, Object> map = getMap();
    @Inject
    private PersonInfoService personInfoService;

    @Inject
    private SysUserService sysUserService;

    /**
     * 添加表头
     */
    @Override
    public void addHeader() {
        Cnd cnd = Cnd.NEW();
        cnd.and(Sqfw_datamange_person::getExcelSeg, "=", "header");
        Sqfw_datamange_person dataMangePerson = super.fetch(cnd);
        List<SqfwExcelOnlineAllDTO> dtoList = new ArrayList<>();
        if (dataMangePerson == null) {
            dataMangePerson = new Sqfw_datamange_person();
        }

        for (String key : map.keySet()) {
            SqfwExcelOnlineAllDTO dto = new SqfwExcelOnlineAllDTO();
            dto.setId(R.UU32());
            dto.setV(map.get(key).toString());
            dto.setD(DateUtil.format(new Date(), DatePattern.NORM_DATETIME_PATTERN));
            dto.setT("s");
            dto.setField(key);
            dtoList.add(dto);
        }
        dataMangePerson.setPersonId("0");
        dataMangePerson.setDelFlag(false);
        dataMangePerson.setExcelData(JSONUtil.toJsonStr(dtoList));
        dataMangePerson.setExcelSeg("header");
        super.insertOrUpdate(dataMangePerson);
    }

    /**
     * 数据清洗转成excel结构
     * @return
     */
    @Override
    public Result<?> dataCleaning(String start, String end) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from shequ.sqfw_person_info where 1=1 ");
        if (StringUtils.isNotBlank(start)) {
            try {
                DateUtil.parse(start, "yyyy-MM-dd"); // 验证日期格式。
            } catch (Exception e) {
                throw new ServiceException("日期格式不正确");
            }
            stringBuilder.append("and ").append("create_date >= '").append(start).append("' ");
        }
        if (StringUtils.isNotBlank(end)) {
            try {
                DateUtil.parse(end, "yyyy-MM-dd"); // 验证日期格式。
            } catch (Exception e) {
                throw new ServiceException("日期格式不正确");
            }
            stringBuilder.append("and ").append("create_date <= '").append(end).append("' ");
        }
        String totalSql = stringBuilder.toString();
        int total = personInfoService.count(Sqls.create(totalSql.replace("*", "count(*)")));
        stringBuilder.append(" limit ").append("${size}").append(" offset ").append("${num}");
        for (int i = 0; i < total; i ++) {
            String querySql = stringBuilder.toString();
            querySql = querySql.replace("${size}", pageSize.toString()).replace("${num}", (pageSize * i) + "");
            List<PersonInfo> personInfoList = personInfoService.listEntity(Sqls.create(querySql));
            log.info("解析人员信息第 "+ (i * pageSize) +" 条到第 "+ ((i * pageSize)+personInfoList.size()) + " 条");

            if (personInfoList.size() == 0) {
                return Result.success();
            }

            List<Sqfw_datamange_person> datamangePersonList = dataAnalysis(personInfoList);

            super.insertOrUpdate(datamangePersonList);
        }
        return Result.success();
    }

    @Override
    public Pagination page(SqfwDataMangePersonVO bean) {
        if (null == bean) {
            bean = new SqfwDataMangePersonVO();
        }
        // 构造查询条件
        List<Sqfw_datamange_person> datamangePersonList = new ArrayList<>();
        // 查询header头
        Cnd cnd = Cnd.NEW();
        cnd.and(Sqfw_datamange_person::getExcelSeg, "=", "header");
        Sqfw_datamange_person datamangePerson = super.fetch(cnd);
        if (datamangePerson == null) {
            return new Pagination();
        }
        datamangePersonList.add(datamangePerson);

        // 获取body数据
        String sql = buildQueryCond(bean, datamangePerson);
        log.info("sql语句："+ sql);
        Pagination pagination = this.listPage(bean.getPageNo(), bean.getPageSize(), Sqls.create(sql));
        List<Sqfw_datamange_person> sqfwOnlines = pagination.getList(Sqfw_datamange_person.class);

        datamangePersonList.addAll(sqfwOnlines);
        pagination.setList(datamangePersonList);
        return pagination;
    }

    /**
     * 获取用户所属区域
     * @return
     */
    @Override
    public TreeDTO getRegion() {
        return CommonCheckUtils.getRegion();
    }

    /**
     * 获取子单位
     * @param id
     * @return
     */
    @Override
    public List<TreeDTO> getChild(String id) {
        return CommonCheckUtils.getChild(id);
    }

    /**
     * 获取户籍所在地
     * @param type
     * @param parent
     * @return
     */
    @Override
    public List<String> getRegAddress(String type, String parent) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select reg_${v} as name from shequ.sqfw_person_info where reg_${v} != '直属' ");
        switch (type) {
            case "province" :
                break;
            case "city" :
                stringBuilder.append(" and reg_province = '").append(parent).append("'");
                break;
            case "district" :
                stringBuilder.append(" and reg_city = '").append(parent).append("'");
                break;
            case "county" :
                stringBuilder.append(" and reg_district = '").append(parent).append("'");
                break;
            case "community" :
                stringBuilder.append(" and reg_county = '").append(parent).append("'");
                break;
        }
        stringBuilder.append(" group by reg_${v}");
        String sql = stringBuilder.toString().replaceAll("\\$\\{v\\}", type);
        log.info("sql语句："+ sql);
        List<String> list = new ArrayList<>();
        List<PersonInfo> personInfoList = personInfoService.listEntity(Sqls.create(sql));
        for (PersonInfo personInfo : personInfoList) {
            if (StringUtils.isNotBlank(personInfo.getName())) {
                list.add(personInfo.getName());
            }
        }
        return list;
    }

    @Override
    public List<String> getHealthStatus() {
       String sql = "select (excel_data -> 13 ->> 'v') as id from shequ.sqfw_datamange_person where excel_seg = 'body' group by (excel_data -> 13 ->> 'v')";
        List<Sqfw_datamange_person> personList = super.listEntity(Sqls.create(sql));
        List<String> list = new ArrayList<>();
        for (Sqfw_datamange_person personInfo : personList) {
            if (StringUtils.isNotBlank(personInfo.getId())) {
                list.add(personInfo.getId());
            }
        }
        return list;
    }

    @Override
    public List<String> getSpecialType() {
        String sql = "select name as id from " +
                "(select jsonb_array_elements(tags::jsonb) as name from shequ.sqfw_datamange_person )a group by name";
        List<Sqfw_datamange_person> personList = super.listEntity(Sqls.create(sql));
        List<String> list = new ArrayList<>();
        for (Sqfw_datamange_person personInfo : personList) {
            if (StringUtils.isNotBlank(personInfo.getId())) {
                list.add(personInfo.getId().replaceAll("\"", ""));
            }
        }
        return list;
    }

    /**
     * 构造查询条件
     *
     * @param bean
     * @return
     */
    private String buildQueryCond(SqfwDataMangePersonVO bean, Sqfw_datamange_person header) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id, excel_data from shequ.sqfw_datamange_person where excel_seg != 'header' and del_flag = false");

        // --------------json字段查询开始
        if (null != bean && StringUtils.isNotEmpty(bean.getUserName())) {
            sql.append(" AND (excel_data -> 0 ->> 'v') like '")
                    .append(bean.getUserName())
                    .append("%'");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getIdNumber())) {
            sql.append(" AND (excel_data -> 2 ->> 'v') = '")
                    .append(bean.getIdNumber())
                    .append("'");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getHealthStatus())) {
            sql.append(" AND (excel_data -> 13 ->> 'v') = '")
                    .append(bean.getHealthStatus())
                    .append("'");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getSpecialType())) {
            sql.append(" AND (");
            String vs[] = bean.getSpecialType().split(",");
            int i = 0;
            for (String v : vs) {
                if (i > 0) {
                    sql.append(" or ");
                }
                sql.append("tags @> '[\"")
                        .append(v)
                        .append("\"]'");
                i ++;
            }
            sql.append(")");
        }

        // 户籍
        if (null != bean && StringUtils.isNotEmpty(bean.getRegProvince())) {
            sql.append(" AND (excel_data -> 36 ->> 'v') = '")
                    .append(bean.getRegProvince())
                    .append("')");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getRegCity())) {
            sql.append(" AND (excel_data -> 37 ->> 'v') = '")
                    .append(bean.getRegCity())
                    .append("'");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getRegDistrict())) {
            sql.append(" AND (excel_data -> 38 ->> 'v') = '")
                    .append(bean.getRegDistrict())
                    .append("'");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getRegCounty())) {
            sql.append(" AND (excel_data -> 39 ->> 'v') = '")
                    .append(bean.getRegCounty())
                    .append("'");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getRegCommunity())) {
            sql.append(" AND (excel_data -> 40 ->> 'v') = '")
                    .append(bean.getRegCommunity())
                    .append("'");
        }

        if (null != bean && bean.getTime() != null && bean.getTime().size() > 0) {
            String start = bean.getTime().get(0);
            String end = bean.getTime().get(1);
            sql.append(" AND update_date >= '")
                    .append(start).append("'");
            sql.append(" and ");
            sql.append(" update_date <= '")
                    .append(end).append("'");
        }

        // --------------json字段查询结束

//        if (null != bean && bean.getTime() != null && bean.getTime().size() > 0) {
//            String start = bean.getTime().get(0);
//            String end = bean.getTime().get(1);
//            JSONArray array = JSONUtil.parseArray(header.getExcelData());
//            sql.append(" and (");
//            for (int i = 0; i < array.size(); i++) {
//                sql.append("(");
//                sql.append("(excel_data -> ").append(i).append(" ->> 'd') > ").append("'").append(start).append("'");
//                sql.append(" and (excel_data -> ").append(i).append(" ->> 'd') < ").append("'").append(end).append("'");
//                sql.append(")");
//                if (i < array.size() - 1) {
//                    sql.append(" or ");
//                }
//            }
//            sql.append(")");
//        }

        // 设置五级
        Criteria cri = Cnd.cri();
        SqlExpressionGroup criSql = cri.where();
        if (StringUtils.isBlank(bean.getUnitId())) {
            CommonCheckUtils.checkAreaRange(criSql);
        } else {
            CommonCheckUtils.setCriSql(criSql, bean.getUnitId());
        }
        String csql = cri.toString();
        csql = csql.replace("where", "").replace("WHERE", "");
        if (StringUtils.isNotBlank(csql)) {
            sql.append(" and ").append(csql);
        }

        return sql.toString();
    }

    /**
     * 数据清洗
     * @param personInfoList
     * @return
     */
    private List<Sqfw_datamange_person> dataAnalysis(List<PersonInfo> personInfoList) {
        List<Sqfw_datamange_person> datamangePersonList = new ArrayList<>();
        for (PersonInfo personInfo : personInfoList) {
            List<SqfwExcelOnlineAllDTO> dtoList = new ArrayList<>();
            Sqfw_datamange_person datamangePerson = new Sqfw_datamange_person();
            for (String f : map.keySet()) {
                SqfwExcelOnlineAllDTO dto = new SqfwExcelOnlineAllDTO();
                String value = getValue(personInfo, f);
                dto.setId(R.UU32());
                dto.setV(value);
                dto.setD(DateUtil.format(personInfo.getCreateDate(), DatePattern.NORM_DATETIME_PATTERN));
                dto.setT("s");
                dto.setField(f);
                dtoList.add(dto);
            }
            datamangePerson.setId(personInfo.getId());
            datamangePerson.setPersonId(personInfo.getId());
            datamangePerson.setExcelData(JSONUtil.toJsonStr(dtoList));
            datamangePerson.setExcelSeg("body");
            datamangePerson.setDelFlag(false);
            datamangePerson.setSpecialMark(personInfo.getSpecialMark());
            datamangePerson.setProvince(personInfo.getProvince());
            datamangePerson.setCity(personInfo.getCity());
            datamangePerson.setDistrict(personInfo.getDistrict());
            datamangePerson.setCounty(personInfo.getCounty());
            datamangePerson.setCommunity(personInfo.getCommunity());
            datamangePerson.setUpdateDate(personInfo.getUpdateDate());

            Set<String> tags = new HashSet<>();
            String value = getValue(personInfo, "special_mark");
            if (StringUtils.isNotBlank(value)) {
                String vs[] = value.split("；");
                for (String v : vs) {
                    String str = v.substring(v.indexOf("（") + 1, v.lastIndexOf("）"));
                    String ss[] = str.split("、");
                    for (String t : ss) {
                        tags.add(t);
                    }
                }
                datamangePerson.setTags(JSONUtil.toJsonStr(tags));
            }

            datamangePersonList.add(datamangePerson);
        }
        return datamangePersonList;
    }

    private String getValue(PersonInfo personInfo, String field) {
        JSONObject jsonObject = JSONUtil.parseObj(personInfo);
        Field[] fields1 = personInfo.getClass().getDeclaredFields();
        for (Field f : fields1) {
            Column column = f.getAnnotation(Column.class);
            if (column != null && field.equals(f.getAnnotation(Column.class).value())) {
                if ("special_mark".equals(field)) {
                    StringBuilder stringBuilder = new StringBuilder();
                    JSONArray jsonArray = jsonObject.getJSONArray(f.getName());
                    if (jsonArray != null && jsonArray.size() > 0) {
                        int m = 0;
                        for (Object o : jsonArray) {
                            JSONObject jsonObject1 = JSONUtil.parseObj(o);
                            if (m > 0) {
                                stringBuilder.append("；");
                            }
                            String v1 = jsonObject1.getStr("businessType");
                            stringBuilder.append(StringUtils.isBlank(v1)?"":v1);
                            JSONArray tags = jsonObject1.getJSONArray("tag");
                            if (tags != null && tags.size() > 0) {
                                stringBuilder.append("（");
                                int n = 0;
                                for (Object o1 : tags) {
                                    JSONObject tag = JSONUtil.parseObj(o1);
                                    if (n > 0) {
                                        stringBuilder.append("、");
                                    }
                                    String v2 = tag.getStr("name");
                                    stringBuilder.append(StringUtils.isBlank(v2)?"":v2);
                                    n ++;
                                }
                                stringBuilder.append("）");
                            }
                            m ++;
                        }
                    }
                    return stringBuilder.toString();
                }
                if ("is_registered".equals(field) || "welfare_status".equals(field)) {
                    Boolean b;
                    try {
                        b = jsonObject.getBool(f.getName());
                    } catch (Exception e) {
                        return "";
                    }
                    if (b) {
                        return "是";
                    } else {
                        return "否";
                    }
                }
                return jsonObject.getStr(f.getName());
            }
        }
        return "";
    }

    /**
     * 读取excel文件，获取表头信息
     * @return
     */
    private static Map<String, Object> getMap() {
        try {
            Map<String, Object> map = new LinkedHashMap<>();
            String filePathStr = getTemplatePathByOS("dataManage/dataManagePerson.xlsx");
            //创建工作簿对象
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePathStr));
            //获取工作簿下sheet的个数
            int sheetNum = xssfWorkbook.getNumberOfSheets();
            //遍历工作簿中的所有数据
            for(int i = 0;i<sheetNum;i++) {
                //读取第i个工作表
                XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
                //获取最后一行的num，即总行数。此处从0开始
                int maxRow = sheet.getLastRowNum();
                for (int row = 0; row <= maxRow; row ++) {
                    //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                    map.put(sheet.getRow(row).getCell(1).toString(), sheet.getRow(row).getCell(0));
                }
            }
            return map;
        } catch (IOException e) {
            e.printStackTrace();
            throw new ServiceException(e.getMessage());
        }
    }
}
